﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Microsoft.Practices.EnterpriseLibrary.Data;
using System.Data.Common;
using System.Data.SqlClient;
using System.Reflection;
using AudioCore.Entity;
using System.Data;

namespace AudioCore.DAO
{
    public abstract class BaseDao : IDisposable
    {
        protected Database db = null;

        /// <summary>
        /// 
        /// </summary>
        protected string TableName { get; set; }

        protected string PrimaryKey { get; set; }

        public BaseDao()
        {
        }

        public BaseDao(string TableName, string PrimaryKey)
        {
            this.TableName = TableName;
            this.PrimaryKey = PrimaryKey;
        }

        public DbConnection OpenConnection()
        {
            if (db == null)
                return null;
            return db.CreateConnection();
        }

        public void CloseConnection(DbConnection connection)
        {
            connection.Close();
        }

        #region sql

        /// <summary>
        /// Insert new Entity
        /// </summary>
        /// <param name="entity">Entity will be inserted</param>
        /// <param name="type">Type of entity</param>
        /// <returns>Numbers of row affected</returns>
        public virtual int InsertEntity(BaseEntity entity, Type type)
        {
            string query = "insert into {0} ({1}) values ({2}) select SCOPE_IDENTITY()";
            string column = string.Empty;
            string values = string.Empty;

            bool isFirst = true;
            DbCommand command = new SqlCommand();
            foreach (PropertyInfo property in type.GetProperties())
            {
                object[] arrMapping = property.GetCustomAttributes(typeof(PropertyEntity), true);
                if (arrMapping == null || arrMapping.Length == 0)
                {
                    continue;
                }
                PropertyEntity objMapping = (PropertyEntity)arrMapping[0];

                //Breaking process when current field is not a column in the database
                if (!objMapping.IsColumn)
                {
                    continue;
                }

                if (!objMapping.ColumnName.ToUpper().Equals(PrimaryKey.ToUpper()))
                {
                    if (isFirst)
                    {
                        column += objMapping.ColumnName;
                        values += "@" + objMapping.ColumnName;
                        isFirst = false;
                    }
                    else
                    {
                        column += ", " + objMapping.ColumnName;
                        values += ", @" + objMapping.ColumnName;
                    }

                    //Add param
                    string paramName = "@" + objMapping.ColumnName;
                    object paramValue = property.GetValue(entity, null);
                    if (paramValue == null)
                    {
                        paramValue = DBNull.Value;
                    }
                    command.Parameters.Add(new SqlParameter(paramName, paramValue));
                }
            }

            query = string.Format(query, TableName, column, values);
            command.CommandText = query;

            int result = int.Parse(db.ExecuteScalar(command).ToString());
            return result;
        }

        /// <summary>
        /// Update entity by it's ID
        /// </summary>
        /// <param name="entity">Entity will be updated</param>
        /// <param name="type">Type of Entity</param>
        /// <returns>Numbers of row affected</returns>
        public virtual int UpdateEntity(BaseEntity entity, Type type)
        {
            string query = "update {0} set {1} where {2} = {3}";
            string values = string.Empty;
            string condition = string.Empty;
            int key = 0;

            bool isFirst = true;
            DbCommand command = new SqlCommand();
            foreach (PropertyInfo property in type.GetProperties())
            {
                object[] arrMapping = property.GetCustomAttributes(typeof(PropertyEntity), true);
                if (arrMapping == null || arrMapping.Length == 0)
                {
                    continue;
                }
                PropertyEntity objMapping = (PropertyEntity)arrMapping[0];

                //Breaking process when current field is not a column in the database
                if (!objMapping.IsColumn)
                {
                    continue;
                }

                object paramValue = property.GetValue(entity, null);
                if (objMapping.ColumnName.ToUpper().Equals(PrimaryKey.ToUpper()))
                {
                    key = int.Parse(paramValue.ToString());
                }
                else
                {
                    if (isFirst)
                    {
                        values += objMapping.ColumnName + "= @" + objMapping.ColumnName;
                        isFirst = false;
                    }
                    else
                    {
                        values += ", " + objMapping.ColumnName + "= @" + objMapping.ColumnName;
                    }

                    string paramName = "@" + objMapping.ColumnName;
                    if (paramValue == null)
                    {
                        paramValue = DBNull.Value;
                    }
                    command.Parameters.Add(new SqlParameter(paramName, paramValue));
                }
            }

            query = string.Format(query, TableName, values, PrimaryKey, key);
            command.CommandText = query;

            int result = db.ExecuteNonQuery(command);
            return result;
        }

        /// <summary>
        /// Delete entity by it's ID
        /// </summary>
        /// <param name="id">ID of entity will be deleted</param>
        /// <returns>Numbers of row affected</returns>
        public virtual int DeleteEntity(int id)
        {
            string query = "Delete from {0} where {1} = {2}";
            query = string.Format(query, TableName, PrimaryKey, id);
            int result = db.ExecuteNonQuery(CommandType.Text, query);
            return result;
        }

        /// <summary>
        /// Select all items in the table
        /// </summary>
        /// <returns>All item in the table</returns>
        public virtual DataTable SelectAllItem()
        {
            string query = "select * from {0}";
            query = string.Format(query, TableName);
            return db.ExecuteDataSet(CommandType.Text, query).Tables[0];
        }

        /// <summary>
        /// Get an item in the table by it's ID
        /// </summary>
        /// <param name="id">ID of the item</param>
        /// <returns>An item in the table which has matched ID</returns>
        public virtual DataTable SelectEntityByID(int id)
        {
            string query = "select * from {0} where {1} = {2}";
            query = string.Format(query, TableName, PrimaryKey, id);
            return db.ExecuteDataSet(CommandType.Text, query).Tables[0];
        }

        /// <summary>
        /// Get a datatable which has the rows matched with the value
        /// </summary>
        /// <param name="columnName">Name of the column will be compared</param>
        /// <param name="value">Value of the column will be compared</param>
        /// <returns>Datatable which has the rows matched with the condition</returns>
        public virtual DataTable SelectEntityByColumnName(string columnName, object value)
        {
            string query = "select * from {0} where {1} = {2}";
            string prColumnName = "@" + columnName;
            query = string.Format(query, TableName, columnName, prColumnName);
            DbCommand command = new SqlCommand(query);
            command.Parameters.Add(new SqlParameter(prColumnName, value));
            return db.ExecuteDataSet(command).Tables[0];
        }

        public virtual DataTable SelectFieldsByColumnName(string[] fieldNames, string columnName, object value)
        {
            string query = "select {0} from {1} where {2} = {3}";
            string sqlFields = fieldNames[0];
            for (int i = 1; i < fieldNames.Length; i++)
            {
                sqlFields += ", " + fieldNames[i];
            }

            string prColumnName = "@" + columnName;
            query = string.Format(query, sqlFields, TableName, columnName, prColumnName);
            DbCommand command = new SqlCommand(query);
            command.Parameters.Add(new SqlParameter(prColumnName, value));
            return db.ExecuteDataSet(command).Tables[0];
        }

        public virtual DataTable SelectEntityByColumnNames(string[] columnNames, object[] values)
        {
            string query = "select * from {0} where {1} = {2}";
            query = string.Format(query, TableName, columnNames[0], "@" + columnNames[0]);
            for (int i = 1; i < columnNames.Length; i++)
            {
                string s = " AND {0}={1}";
                query += string.Format(s, columnNames[i], "@" + columnNames[i]);
            }

            DbCommand command = new SqlCommand(query);
            for (int i = 0; i < columnNames.Length; i++)
            {
                command.Parameters.Add(new SqlParameter("@" + columnNames[i], values[i]));
            }

            return db.ExecuteDataSet(command).Tables[0];
        }

        public virtual DataTable SelectFieldsByColumnNames(string[] fieldNames, string[] columnNames, object[] values)
        {
            string query = "select {0} from {1} where {2} = {3}";

            string sqlFields = fieldNames[0];
            for (int i = 1; i < fieldNames.Length; i++)
            {
                sqlFields += ", " + fieldNames[i];
            }

            query = string.Format(query, sqlFields, TableName, columnNames[0], "@" + columnNames[0]);
            for (int i = 1; i < columnNames.Length; i++)
            {
                string s = " AND {0}={1}";
                query += string.Format(s, columnNames[i], "@" + columnNames[i]);
            }

            DbCommand command = new SqlCommand(query);
            for (int i = 0; i < columnNames.Length; i++)
            {
                command.Parameters.Add(new SqlParameter("@" + columnNames[i], values[i]));
            }

            return db.ExecuteDataSet(command).Tables[0];
        }

        /// <summary>
        /// Search item in the table by condition and paging the result
        /// </summary>
        /// <param name="fieldNames">List of column will be returned. Using * to select all</param>
        /// <param name="equalColumn">List of column will be compared</param>
        /// <param name="equalValues">List of value to compare with each column</param>
        /// <param name="searchColumn">List of column will be searched in.</param>
        /// <param name="keyword">Keyword to search</param>
        /// <param name="pageIndex">Page index</param>
        /// <param name="pageSize">Page size</param>
        /// <param name="orderColumn">The column using to indexing order of result</param>
        /// <returns>Return two data tables. Table[0] is count of search condition, table[1] is list of result</returns>
        public virtual DataTable SearchEntityByPaging(string[] fieldNames,
            string[] equalColumn, object[] equalValues,
            string[] searchColumn, string keyword,
            int pageIndex, int pageSize, string orderColumn, out int recordCount)
        {
            string separate = string.Empty;
            int startRow = pageIndex * pageSize;
            int endRow = startRow + pageSize;
            DbCommand command = new SqlCommand();

            string query = "select count({0}) {1} select tmp.* from "
                + " (select row_number() over (Order By {2} desc) as rowIndex, {3} {1}) as tmp "
                + " where (rowIndex > {4} and rowIndex <= {5}) ";


            string fields = string.Empty;
            for (int i = 0; i < fieldNames.Length; i++)
            {
                fields += fieldNames[i] + separate;
                separate = ", ";
            }

            string strCondition = string.Empty;
            if (equalColumn != null && equalColumn.Length != 0)
            {
                for (int i = 0; i < equalColumn.Length; i++)
                {
                    if (equalValues[i] != null)
                    {
                        strCondition += string.Format(" AND {0}={1} ", equalColumn[i], "@" + equalColumn[i]);
                        command.Parameters.Add(new SqlParameter("@" + equalColumn[i], equalValues[i]));
                    }
                }
            }

            keyword = keyword.Trim();
            if (!string.IsNullOrEmpty(keyword) && searchColumn != null && searchColumn.Length != 0)
            {
                strCondition += " AND (1<>1 ";
                for (int i = 0; i < searchColumn.Length; i++)
                {
                    strCondition += string.Format(" OR {0} like '%' + @KeyWord + '%' ", searchColumn[i]);
                }
                command.Parameters.Add(new SqlParameter("@KeyWord", keyword));
                strCondition += ")";
            }

            string strFrom = " FROM {0} WHERE 1 = 1 {1}";
            strFrom = string.Format(strFrom, TableName, strCondition);

            query = string.Format(query, PrimaryKey, strFrom, orderColumn, fields, startRow, endRow);
            command.CommandText = query;
            DataSet dst = db.ExecuteDataSet(command);
            recordCount = (int)dst.Tables[0].Rows[0][0];
            return dst.Tables[1];
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="sqlQuery"></param>
        /// <param name="fieldNames"></param>
        /// <param name="equalColumn"></param>
        /// <param name="equalValues"></param>
        /// <param name="searchColumn"></param>
        /// <param name="keyword"></param>
        /// <param name="pageIndex"></param>
        /// <param name="pageSize"></param>
        /// <param name="orderColumn"></param>
        /// <param name="recordCount"></param>
        /// <returns></returns>
        public virtual DataTable SearchItemByPaging(string sqlQuery, string[] fieldNames,
            string[] equalColumn, object[] equalValues,
            string[] searchColumn, string keyword,
            int pageIndex, int pageSize, string orderColumn, out int recordCount)
        {
            string separate = string.Empty;
            int startRow = pageIndex * pageSize;
            int endRow = startRow + pageSize;
            DbCommand command = new SqlCommand();

            string query = "select count({0}) {1} select tmp.* from "
                + " (select row_number() over (Order By {2} desc) as rowIndex, {3} {1}) as tmp "
                + " where (rowIndex > {4} and rowIndex <= {5}) ";


            string fields = string.Empty;
            for (int i = 0; i < fieldNames.Length; i++)
            {
                fields += fieldNames[i] + separate;
                separate = ", ";
            }

            string strCondition = string.Empty;
            if (equalColumn != null && equalColumn.Length != 0)
            {
                for (int i = 0; i < equalColumn.Length; i++)
                {
                    if (equalValues[i] != null)
                    {
                        strCondition += string.Format(" AND {0}={1} ", equalColumn[i], "@" + equalColumn[i]);
                        command.Parameters.Add(new SqlParameter("@" + equalColumn[i], equalValues[i]));
                    }
                }
            }

            keyword = keyword.Trim();
            if (!string.IsNullOrEmpty(keyword) && searchColumn != null && searchColumn.Length != 0)
            {
                strCondition += " AND (1<>1 ";
                for (int i = 0; i < searchColumn.Length; i++)
                {
                    strCondition += string.Format(" OR {0} like '%' + @KeyWord + '%' ", searchColumn[i]);
                }
                command.Parameters.Add(new SqlParameter("@KeyWord", keyword));
                strCondition += ")";
            }

            string strFrom = " FROM ({0}) AS VirtualTable WHERE 1 = 1 {1} ";
            strFrom = string.Format(strFrom, sqlQuery, strCondition);

            query = string.Format(query, "*", strFrom, orderColumn, fields, startRow, endRow);
            command.CommandText = query;
            DataSet dst = db.ExecuteDataSet(command);
            recordCount = (int)dst.Tables[0].Rows[0][0];
            return dst.Tables[1];
        }

        /// <summary>
        /// Update Columns by ID
        /// </summary>
        /// <param name="itemID">Id of entity will be updated</param>
        /// <param name="columnNames">Array Name of columns will be updated</param>
        /// <param name="values">Array value of columns will be updated</param>
        /// <returns>Numbers of row affected</returns>
        public virtual int UpdateColumnsByID(int itemID, string[] columnNames, object[] values)
        {
            string query = "UPDATE {0} set {1} where {2} = {3}";

            string updateField = string.Format("{0}={1}", columnNames[0], "@" + columnNames[0]);
            for (int i = 1; i < columnNames.Length; i++)
            {
                string s = " , {0}={1} ";
                updateField += string.Format(s, columnNames[i], "@" + columnNames[i]);
            }

            DbCommand command = new SqlCommand();
            for (int i = 0; i < columnNames.Length; i++)
            {
                command.Parameters.Add(new SqlParameter("@" + columnNames[i], values[i]));
            }

            query = string.Format(query, TableName, updateField, PrimaryKey, itemID);
            command.CommandText = query;

            return db.ExecuteNonQuery(command);
        }

        public virtual int DeleteItemByColumnNames(string[] columnNames, object[] values)
        {
            string query = "Delete from {0} where {1} = {2}";
            query = string.Format(query, TableName, columnNames[0], "@" + columnNames[0]);
            for (int i = 1; i < columnNames.Length; i++)
            {
                string s = " AND {0}={1}";
                query += string.Format(s, columnNames[i], "@" + columnNames[i]);
            }

            DbCommand command = new SqlCommand(query);
            for (int i = 0; i < columnNames.Length; i++)
            {
                command.Parameters.Add(new SqlParameter("@" + columnNames[i], values[i]));
            }

            return db.ExecuteNonQuery(command);
        }

        #endregion


        #region IDisposable Members

        public void Dispose()
        {
            db = null;
            GC.SuppressFinalize(this);
        }

        #endregion
    }
}
